#-*- encoding: utf-8 -*-
import csv, MySQLdb, sys, urllib
import math

def mysql_database(sql_str, values, code): # Database
    conn = MySQLdb.connect(host='127.0.0.1', user='root',passwd='andy1991') #数据库连接参数
    cursor = conn.cursor()
    conn.select_db('stock')#数据库名称。都用Stock
    
    try:
        sql = "drop table `stock`.`"+str(code)+ "`;"
        cursor.execute(sql)
        sql = "create table if not exists " + str(code) + "(  `ID` int(11) NOT NULL AUTO_INCREMENT,  `Date` date NOT NULL,  `Open` float NOT NULL,  `High` float NOT NULL,  `Low` float NOT NULL,  `Close` float NOT NULL,  `Volume` bigint(20) DEFAULT NULL,  `Adj` float NOT NULL,  PRIMARY KEY (`ID`)) ENGINE=InnoDB"
        cursor.execute(sql)
        conn.commit()
    except:
        sql = "create table if not exists " + str(code) + "(  `ID` int(11) NOT NULL AUTO_INCREMENT,  `Date` date NOT NULL,  `Open` float NOT NULL,  `High` float NOT NULL,  `Low` float NOT NULL,  `Close` float NOT NULL,  `Volume` bigint(20) DEFAULT NULL,  `Adj` float NOT NULL,  PRIMARY KEY (`ID`)) ENGINE=InnoDB"
        cursor.execute(sql)
        conn.commit()
    cursor.executemany(sql_str, values)
    conn.commit()
    
    cursor.close()
    conn.close()

def domainwriter(reader):
    values=[]
    for obj1 in reader:
        values.append(( obj1['Date'], obj1['Open'], obj1['High'], obj1['Low'], obj1['Close'], obj1['Volume'], obj1['Adj Close'], ))  #csv title
    return values

def linkdb(codesql, conn, cursor):
    try:
        sql = "drop table `stock`.`data"+str(codesql)+ "`;"
        cursor.execute(sql)
        sql = "CREATE TABLE `data"+ str(codesql) +"` (  `identifier` int(11) NOT NULL AUTO_INCREMENT,  `id` int(11) NOT NULL,  `value` double NOT NULL,  PRIMARY KEY (`identifier`)) ENGINE=InnoDB"
        cursor.execute(sql)
        conn.commit()
    except:
        sql = "CREATE TABLE `data"+ str(codesql) +"` (  `identifier` int(11) NOT NULL AUTO_INCREMENT,  `id` int(11) NOT NULL,  `value` double NOT NULL,  PRIMARY KEY (`identifier`)) ENGINE=InnoDB"
        cursor.execute(sql)
        conn.commit()

def mdcode(str1): # Convert
    for c in ('utf-8', 'gbk', 'gb2312'):
        try:
            return str1.decode(c).encode('utf-8')
        except:
            pass
    return 'unknown'

#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

if __name__ == "__main__":

    for nowid in range(1, 49):   
        print "Start data request "+ str(nowid)
        try:
            connread = MySQLdb.connect(host='localhost',user='root',passwd='andy1991',db='stock')#读取列表，db=Nasdaq或者stock
        except Exception, e:
            print e
            sys.exit()
            
        cursorread = connread.cursor()
        sql = "select code from list where id = "+str(nowid)
        cursorread.execute(sql)
        codelist = cursorread.fetchall()
        code = codelist[0][0]
        
        sql = "select name from list where id = "+str(nowid)
        cursorread.execute(sql)
        namelist = cursorread.fetchall()
        name = namelist[0][0]
        
        cursorread.close()
        connread.close()
        
        try:
            FILE_PATH = 'yahoowebdata.csv' #路径
            urllib.urlretrieve("http://ichart.finance.yahoo.com/table.csv?s="+ str(code) +"&a=05&b=2&c=1990&d=00&e=1&f=2012&g=d&ignore=.csv", FILE_PATH)
        except Exception, e:
            print sys.exc_info()[0],sys.exc_info()[1]
            sys.exit()
        print "Start SQL request of " + str(code) + " " + str(name)
        
        if cmp(".hk",code[4:6]):
            codesql = "hk" + filter(str.isdigit, code)
        else:
            codesql =code
        
        INSERT_SQL = {'domain': 'insert into ' + str(codesql) + ' values(NULL, %s, %s, %s, %s, %s, %s, %s )'}  #有个自增的ID列用Null区分开，test2为表名，平时为股票代号
        INSERT_VALUES = {'domain': domainwriter}
    
        try:
            fd = open(FILE_PATH)            # get file connection
            dialect = csv.Sniffer().sniff(fd.readline())
            fd.seek(0)
            reader = csv.DictReader(fd, dialect = dialect)

            mysql_database(INSERT_SQL['domain'], INSERT_VALUES['domain'](reader), codesql) # insert into MySQL database
            print "Complete SQL request of " + str(code)
        except:
            print sys.exc_info()[0],sys.exc_info()[1]
        finally:
            fd.close()
        
        try:
            connread = MySQLdb.connect(host='localhost',user='root',passwd='andy1991',db='stock')#读取列表，db=Nasdaq或者stock
            connwrite = MySQLdb.connect(host='localhost',user='root',passwd='andy1991',db='stock')#写入股票表，db=stock
        except Exception, e:
            print e
            sys.exit()
        
        cursorread = connread.cursor()
        cursorwrite = connwrite.cursor()
        sqlread = "select adj from "+ str(codesql) + " where id % 8 = 1"
        cursorread.execute(sqlread)
        pricelist = cursorread.fetchall()
        cursorread.close()
        connread.close()
        linkdb(codesql, connwrite,  cursorwrite)
        unum=[]
        
        for lpid in range(0, 30):
            unum.append(math.log(pricelist[lpid+1][0]/pricelist[lpid][0],math.e))
            sql = "insert into data"+ str(codesql) + "(identifier, id, value) values (null,'%s', %s)" % (lpid, unum[lpid])
            cursorwrite.execute(sql)
            connwrite.commit()
        
        s1=0
        s2=0
        for count in unum:
            s1 += count
            s2 += count ** 2
        
        s = math.sqrt(1.0/30.0*s2 - 1.0/870.0*s1**2)
        sql = "insert into data"+ str(codesql) + "(identifier, id, value) values (null,'%s', %s)" % (100, s)
        cursorwrite.execute(sql)
        connwrite.commit()
        #T=0.0323886, sqrtT=0.179968330547, n=30, 每8个元素一个周期，一年31个采样点，每个相隔12天
        Timelen=0.0323886
        miu=0.169
        sigma = s/math.sqrt(Timelen)
        sql = "insert into data"+ str(codesql) + "(identifier, id, value) values (null,'%s', %s)" % (101, sigma)
        cursorwrite.execute(sql)
        connwrite.commit()
        
        temp1=math.sqrt(s/Timelen)
        lower = miu - temp1-(sigma**2)/2.0
        upper = miu+ temp1-(sigma**2)/2.0
        pricehigh=pricelist[0][0] * math.e ** (upper*Timelen)
        pricelow= pricelist[0][0] * math.e ** (lower*Timelen)
        
        sql = "insert into data"+ str(codesql) + "(identifier, id, value) values (null,'%s', %s)" % (102, pricelow)
        cursorwrite.execute(sql)
        connwrite.commit()
        sql = "insert into data"+ str(codesql) + "(identifier, id, value) values (null,'%s', %s)" % (103, pricehigh)
        cursorwrite.execute(sql)
        connwrite.commit()
        cursorwrite.close()
        connwrite.close()
    #End of Loop
    print "Predicted Price is saved in MySQL"#-*- encoding: utf-8 -*-
import csv, MySQLdb, sys, urllib
import math

def mysql_database(sql_str, values, code): # Database
    conn = MySQLdb.connect(host='127.0.0.1', user='root',passwd='andy1991') #数据库连接参数
    cursor = conn.cursor()
    conn.select_db('stock')#数据库名称。都用Stock
    
    try:
        sql = "drop table `stock`.`"+str(code)+ "`;"
        cursor.execute(sql)
        sql = "create table if not exists " + str(code) + "(  `ID` int(11) NOT NULL AUTO_INCREMENT,  `Date` date NOT NULL,  `Open` float NOT NULL,  `High` float NOT NULL,  `Low` float NOT NULL,  `Close` float NOT NULL,  `Volume` bigint(20) DEFAULT NULL,  `Adj` float NOT NULL,  PRIMARY KEY (`ID`)) ENGINE=InnoDB"
        cursor.execute(sql)
        conn.commit()
    except:
        sql = "create table if not exists " + str(code) + "(  `ID` int(11) NOT NULL AUTO_INCREMENT,  `Date` date NOT NULL,  `Open` float NOT NULL,  `High` float NOT NULL,  `Low` float NOT NULL,  `Close` float NOT NULL,  `Volume` bigint(20) DEFAULT NULL,  `Adj` float NOT NULL,  PRIMARY KEY (`ID`)) ENGINE=InnoDB"
        cursor.execute(sql)
        conn.commit()
    cursor.executemany(sql_str, values)
    conn.commit()
    
    cursor.close()
    conn.close()

def domainwriter(reader):
    values=[]
    for obj1 in reader:
        values.append(( obj1['Date'], obj1['Open'], obj1['High'], obj1['Low'], obj1['Close'], obj1['Volume'], obj1['Adj Close'], ))  #csv title
    return values

def linkdb(codesql, conn, cursor):
    try:
        sql = "drop table `stock`.`data"+str(codesql)+ "`;"
        cursor.execute(sql)
        sql = "CREATE TABLE `data"+ str(codesql) +"` (  `identifier` int(11) NOT NULL AUTO_INCREMENT,  `id` int(11) NOT NULL,  `value` double NOT NULL,  PRIMARY KEY (`identifier`)) ENGINE=InnoDB"
        cursor.execute(sql)
        conn.commit()
    except:
        sql = "CREATE TABLE `data"+ str(codesql) +"` (  `identifier` int(11) NOT NULL AUTO_INCREMENT,  `id` int(11) NOT NULL,  `value` double NOT NULL,  PRIMARY KEY (`identifier`)) ENGINE=InnoDB"
        cursor.execute(sql)
        conn.commit()

def mdcode(str1): # Convert
    for c in ('utf-8', 'gbk', 'gb2312'):
        try:
            return str1.decode(c).encode('utf-8')
        except:
            pass
    return 'unknown'

#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

if __name__ == "__main__":

    for nowid in range(1, 49):   
        print "Start data request "+ str(nowid)
        try:
            connread = MySQLdb.connect(host='localhost',user='root',passwd='andy1991',db='stock')#读取列表，db=Nasdaq或者stock
        except Exception, e:
            print e
            sys.exit()
            
        cursorread = connread.cursor()
        sql = "select code from list where id = "+str(nowid)
        cursorread.execute(sql)
        codelist = cursorread.fetchall()
        code = codelist[0][0]
        
        sql = "select name from list where id = "+str(nowid)
        cursorread.execute(sql)
        namelist = cursorread.fetchall()
        name = namelist[0][0]
        
        cursorread.close()
        connread.close()
        
        try:
            FILE_PATH = 'yahoowebdata.csv' #路径
            urllib.urlretrieve("http://ichart.finance.yahoo.com/table.csv?s="+ str(code) +"&a=05&b=2&c=1990&d=00&e=1&f=2012&g=d&ignore=.csv", FILE_PATH)
        except Exception, e:
            print sys.exc_info()[0],sys.exc_info()[1]
            sys.exit()
        print "Start SQL request of " + str(code) + " " + str(name)
        
        if cmp(".hk",code[4:6]):
            codesql = "hk" + filter(str.isdigit, code)
        else:
            codesql =code
        
        INSERT_SQL = {'domain': 'insert into ' + str(codesql) + ' values(NULL, %s, %s, %s, %s, %s, %s, %s )'}  #有个自增的ID列用Null区分开，test2为表名，平时为股票代号
        INSERT_VALUES = {'domain': domainwriter}
    
        try:
            fd = open(FILE_PATH)            # get file connection
            dialect = csv.Sniffer().sniff(fd.readline())
            fd.seek(0)
            reader = csv.DictReader(fd, dialect = dialect)

            mysql_database(INSERT_SQL['domain'], INSERT_VALUES['domain'](reader), codesql) # insert into MySQL database
            print "Complete SQL request of " + str(code)
        except:
            print sys.exc_info()[0],sys.exc_info()[1]
        finally:
            fd.close()
        
        try:
            connread = MySQLdb.connect(host='localhost',user='root',passwd='andy1991',db='stock')#读取列表，db=Nasdaq或者stock
            connwrite = MySQLdb.connect(host='localhost',user='root',passwd='andy1991',db='stock')#写入股票表，db=stock
        except Exception, e:
            print e
            sys.exit()
        
        cursorread = connread.cursor()
        cursorwrite = connwrite.cursor()
        sqlread = "select adj from "+ str(codesql) + " where id % 8 = 1"
        cursorread.execute(sqlread)
        pricelist = cursorread.fetchall()
        cursorread.close()
        connread.close()
        linkdb(codesql, connwrite,  cursorwrite)
        unum=[]
        
        for lpid in range(0, 30):
            unum.append(math.log(pricelist[lpid+1][0]/pricelist[lpid][0],math.e))
            sql = "insert into data"+ str(codesql) + "(identifier, id, value) values (null,'%s', %s)" % (lpid, unum[lpid])
            cursorwrite.execute(sql)
            connwrite.commit()
        
        s1=0
        s2=0
        for count in unum:
            s1 += count
            s2 += count ** 2
        
        s = math.sqrt(1.0/30.0*s2 - 1.0/870.0*s1**2)
        sql = "insert into data"+ str(codesql) + "(identifier, id, value) values (null,'%s', %s)" % (100, s)
        cursorwrite.execute(sql)
        connwrite.commit()
        #T=0.0323886, sqrtT=0.179968330547, n=30, 每8个元素一个周期，一年31个采样点，每个相隔12天
        Timelen=0.0323886
        miu=0.169
        sigma = s/math.sqrt(Timelen)
        sql = "insert into data"+ str(codesql) + "(identifier, id, value) values (null,'%s', %s)" % (101, sigma)
        cursorwrite.execute(sql)
        connwrite.commit()
        
        temp1=math.sqrt(s/Timelen)
        lower = miu - temp1-(sigma**2)/2.0
        upper = miu+ temp1-(sigma**2)/2.0
        pricehigh=pricelist[0][0] * math.e ** (upper*Timelen)
        pricelow= pricelist[0][0] * math.e ** (lower*Timelen)
        
        sql = "insert into data"+ str(codesql) + "(identifier, id, value) values (null,'%s', %s)" % (102, pricelow)
        cursorwrite.execute(sql)
        connwrite.commit()
        sql = "insert into data"+ str(codesql) + "(identifier, id, value) values (null,'%s', %s)" % (103, pricehigh)
        cursorwrite.execute(sql)
        connwrite.commit()
        cursorwrite.close()
        connwrite.close()
    #End of Loop
    print "Predicted Price is saved in MySQL"
